package cn.biplam.common.file;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import cn.biplam.common.client.Log;
import cn.biplam.common.client.Log.Log_Level;
import cn.biplam.common.file.importfile.ExcelFileSheet;
import cn.biplam.common.wrapper.ErrorMessage;
import cn.biplam.common.wrapper.KvMap;

/**
 * Excel格式文本文件读取
 * @author WangSong 2009-7-28上午10:29:06
 * @version 1.0
 */
public class ExcelFile {

	/** Excel路径名称*/
	private String filePath;
	/** 文件输入流对象*/
	private FileInputStream file = null;
	/** Excel对象*/
	private HSSFWorkbook workbook;

	/**
	 * 构造方法
	 * @param filePath  文件的名称(含路径)
	 * @throws ErrorMessage 
	 */
	public ExcelFile(String filePath) throws ErrorMessage{
		this.filePath = filePath;
		prepare();
	}

	/**
	 * 初始化环境
	 * @throws ErrorMessage 
	 */
	private void prepare() throws ErrorMessage{
		try {
			file = new FileInputStream(filePath);
			workbook = new HSSFWorkbook(file);
		} catch (FileNotFoundException e) {
			throw new ErrorMessage(e);
		} catch (IOException e) {
			throw new ErrorMessage(e);
		}
	}

	/**
	 * 取Excel的Sheet的数量
	 * @return Sheet的数量
	 */
	public int getSheetSize(){
		return workbook.getNumberOfSheets();
	}

	/**
	 * 取行数
	 * @param sheet
	 * @return
	 * @throws ErrorMessage
	 */
	public static int getRowSize(HSSFSheet sheet) throws ErrorMessage{
		if(sheet==null){
			throw new ErrorMessage("HSSFSheet is null!");
		}
		return sheet.getPhysicalNumberOfRows();
	}

	/**
	 * 取sheet对象
	 * @param index 索引 从0开始
	 * @return sheet对象
	 * @throws ErrorMessage 
	 */
	public HSSFSheet getSheet(int index) throws ErrorMessage{
		/** 判断sheet的索引是否越界*/
		if(getSheetSize()>index){
			return workbook.getSheetAt(index);
		}else{
			throw new ErrorMessage("out of sheet size! sheet size:"+getSheetSize()+",index:"+index);
		}
	}

	/**
	 * 取Excel行对象
	 * @param sheetIndex sheet的索引号 从0开始
	 * @param rowIndex   行的索引号   从0开始
	 * @return  Excel行对象
	 * @throws ErrorMessage
	 */
	public HSSFRow getRow(int sheetIndex,int rowIndex) throws ErrorMessage{
		HSSFSheet sheet = getSheet(sheetIndex);
		/** 判断行的索引是否越界*/
		if(getRowSize(sheet)>rowIndex){
			return sheet.getRow(rowIndex);
		}else{
			throw new ErrorMessage("out of row size!sheet index:"+sheetIndex+", row size:"+getRowSize(sheet)+",rowIndex:"+rowIndex);
		}
	}
	
	/**
	 * 取Excel行对象
	 * @param sheetIndex sheet的索引号 从0开始
	 * @param rowIndex   行的索引号   从0开始
	 * @return  Excel行对象
	 * @throws ErrorMessage
	 */
	public static HSSFRow getRow(HSSFSheet sheet,int rowIndex) throws ErrorMessage{
		/** 判断行的索引是否越界*/
		if(getRowSize(sheet)>rowIndex){
			return sheet.getRow(rowIndex);
		}else{
			throw new ErrorMessage("out of row size!sheet:"+sheet.getNumMergedRegions()+", row size:"+getRowSize(sheet)+",rowIndex:"+rowIndex);
		}
	}
	
	/**
	 * 取Excel指定列的字符串
	 * @param sheetIndex sheet的索引号 从0开始
	 * @param rowIndex   行的索引号 从0开始
	 * @param colIndex   列的索引号 从0开始
	 * @return 指定列的字符串
	 * @throws ErrorMessage
	 */
	@SuppressWarnings("deprecation")
	public HSSFCell getCell(int sheetIndex,int rowIndex,int colIndex) throws ErrorMessage{
		HSSFRow row = getRow(sheetIndex, rowIndex);
		HSSFCell cell = row.getCell((short)colIndex);
		return cell;
	}
	
	/**
	 * 取指定单元格的内容
	 * @param row  行对象 
	 * @param colIndex 列索引号 从0开始
	 * @return 单元格的内容
	 * @throws ErrorMessage
	 */
	@SuppressWarnings("deprecation")
	public static HSSFCell getCell(HSSFRow row,int colIndex) throws ErrorMessage{
		HSSFCell cell = row.getCell((short)colIndex);
		return cell;
	}
	
	/**
	 * 取指定单元格的内容
	 * @param sheet      sheet对象
	 * @param rowIndex   行的索引号 从0开始
	 * @param colIndex   列的索引号 从0开始
	 * @return
	 * @throws ErrorMessage
	 */
	@SuppressWarnings("deprecation")
	public static HSSFCell getCell(HSSFSheet sheet,int rowIndex,int colIndex) throws ErrorMessage{
		HSSFRow row = getRow(sheet, rowIndex);
		HSSFCell cell = null;
		if(row!=null){
			cell = row.getCell((short)colIndex);
		}
		return cell;
	}

	/**
	 * 取Excel指定列的字符串
	 * @param sheetIndex sheet的索引号 从0开始
	 * @param rowIndex   行的索引号 从0开始
	 * @param colIndex   列的索引号 从0开始
	 * @return 指定列的字符串
	 * @throws ErrorMessage
	 */
	@SuppressWarnings("deprecation")
	public String getCellContent(int sheetIndex,int rowIndex,int colIndex) throws ErrorMessage{
		HSSFRow row = getRow(sheetIndex, rowIndex);
		HSSFCell cell = row.getCell((short)colIndex);
		return getContent(cell);
	}
	
	/**
	 * 取指定单元格的内容
	 * @param row  行对象 
	 * @param colIndex 列索引号 从0开始
	 * @return 单元格的内容
	 * @throws ErrorMessage
	 */
	@SuppressWarnings("deprecation")
	public static String getCellContent(HSSFRow row,int colIndex) throws ErrorMessage{
		HSSFCell cell = row.getCell((short)colIndex);
		return getContent(cell);
	}
	
	/**
	 * 取指定单元格的内容
	 * @param sheet      sheet对象
	 * @param rowIndex   行的索引号 从0开始
	 * @param colIndex   列的索引号 从0开始
	 * @return
	 * @throws ErrorMessage
	 */
	@SuppressWarnings("deprecation")
	public static String getCellContent(HSSFSheet sheet,int rowIndex,int colIndex) throws ErrorMessage{
		HSSFRow row = getRow(sheet, rowIndex);
		HSSFCell cell = null;
		if(row!=null){
			cell = row.getCell((short)colIndex);
		}
		return getContent(cell);
	}
	
	/**
	 * 取指定单元格的内容
	 * @param sheet      sheet对象
	 * @param rowIndex   行的索引号 从0开始
	 * @param colIndex   列的索引号 从0开始
	 * @return
	 * @throws ErrorMessage
	 */
	@SuppressWarnings("deprecation")
	public static String getCellContentUnion(HSSFSheet sheet,int rowIndex,int colIndex) throws ErrorMessage{
		HSSFRow row = getRow(sheet, rowIndex);
		int colIndexFirst = row.getFirstCellNum();;
		int colIndexLast = row.getLastCellNum();
		HSSFCell cell = null;
		StringBuilder sb = new StringBuilder();
		for(int i=rowIndex;i<=(rowIndex+colIndexLast);i++){
			if(row!=null){
			
				cell = row.getCell((short)i);
				if(getContent(cell)!=null){
					if(i!=colIndexFirst){
						sb.append("\n");
					}
					sb.append(getContent(cell));
				}
			}
		}
		return sb.toString();
	}
	
	/**
	 * 关闭输入输出流
	 * @throws ErrorMessage 
	 */
	public void close() throws ErrorMessage{
		try {
			if(file!=null){
				file.close();
			}
		} catch (IOException e) {
			throw new ErrorMessage(e);
		}
	}
	
	/**
	 * 根据取单元格内容
	 * @param cell 单元格
	 * @return
	 * @throws ErrorMessage
	 */
	private static String getContent(HSSFCell cell) throws ErrorMessage{
		if(cell!=null){
			if(cell.getCellType()==HSSFCell.CELL_TYPE_STRING){
				return cell.toString().trim();	
			}else if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
				return String.valueOf(cell.getNumericCellValue());
			}else if(cell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN){
				return String.valueOf(cell.getBooleanCellValue());
			}else if(cell.getCellType()==HSSFCell.CELL_TYPE_BLANK){
				return null;
			}else{
				throw new ErrorMessage("cell Type is not found:"+cell.getCellType());
			}
		}else{
			throw new ErrorMessage("cell is null !");
		}
	}
	
	@SuppressWarnings("deprecation")
	public static void main(String[] arg) throws ErrorMessage{
		ExcelFile file = new ExcelFile("D:\\项目\\biplam资料\\Excel导入格式\\表结构信息的导入\\表结构导入.xls");
		HSSFSheet sheet = file.getSheet(0);
		int rowSize = ExcelFile.getRowSize(sheet);
		
		KvMap<Integer, KvMap<String,String>> fields = new KvMap<Integer, KvMap<String,String>>();
		KvMap<String, String> colInfo = null;
		KvMap<String, String> colInfoTemp = null;
		String columnName = null;
		String columnCName = null;
		String columnComment = null;
		int c=0;
		for(int i=4;i<rowSize;i++){
			try {
				columnName = ExcelFile.getCellContent(sheet, i, 1);
				columnCName = ExcelFile.getCellContent(sheet, i, 2);
				columnComment = ExcelFile.getCellContent(sheet, i, 3);
				if(columnName!=null && !"".equals(columnName.trim())){
					colInfo = new KvMap<String, String>();
					colInfoTemp = colInfo;
					colInfo.put(ExcelFileSheet.Name.ColumnName.toString(), columnName);
					colInfo.put(ExcelFileSheet.Name.ColumnCName.toString(), columnCName);
					colInfo.put(ExcelFileSheet.Name.ColumnComment.toString(), columnComment);
					fields.put(c, colInfo);
					c++;
				}else{
					if(columnName!=null){
						colInfoTemp.put(ExcelFileSheet.Name.ColumnName.toString(),
								colInfoTemp.getByKey(ExcelFileSheet.Name.ColumnName.toString())+columnName);
					}
					if(columnCName!=null){
						colInfoTemp.put(ExcelFileSheet.Name.ColumnCName.toString(),
								colInfoTemp.getByKey(ExcelFileSheet.Name.ColumnCName.toString())+columnCName);
					}
					if(columnComment!=null){
						colInfoTemp.put(ExcelFileSheet.Name.ColumnComment.toString(),
								colInfoTemp.getByKey(ExcelFileSheet.Name.ColumnComment.toString())+columnComment);
					}
				}
			} catch (ErrorMessage e) {
				Log.write(e,Log_Level.Error);
			}
		}
	}
}
